package com.budwk.app.sqfw.services.customservice;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.json.JSONUtil;
import com.budwk.app.sqfw.component.CommunityStreetComponent;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.sqfw.vo.ComDataStatisticsVO;
import com.budwk.app.sqfw.vo.SqfwCustomForAdminCommonVO;
import com.budwk.app.sys.services.SysUnitService;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.adminUtils.CommonCheckUtils;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.constant.RedisConstant;
import com.budwk.starter.common.page.Pagination;
import com.budwk.starter.excel.utils.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Criteria;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.integration.jedis.RedisService;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;

import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;

/**
 * @program: dfxy-community
 * @description: 社区数据统计服务
 * @author: LJT
 * @create: 2024-07-16 15:56
 **/
@IocBean
@Slf4j
public class ComDataStatisticsService {
    @Inject
    private SqfwFormsummaryNewService sqfwFormsummaryNewService;
    @Inject
    private SysUserService sysUserService;
    @Inject
    private SysUnitService sysUnitService;
    @Inject
    private CommunityStreetComponent communityStreetComponent;

    @Inject
    private RedisService redisService;

    /**
     * 获取每日数据变化情况统计
     *
     * @param bean
     * @return
     */
    public Pagination pageForSummaryUser(SqfwCustomForAdminCommonVO bean) {
        long startTime = System.currentTimeMillis();
        String startTimeKey = "";
        String endTimeKey = "";
        if (ObjectUtil.isNotEmpty(bean.getQueryStartTime())) {
            startTimeKey = bean.getQueryStartTime().toString();
            endTimeKey = bean.getQueryEndTime().toString();
        }
        String cacheKey = RedisConstant.WKCACHE + "data_statistic:" + startTimeKey + endTimeKey + "_" + bean.getPageNo() + "_" + bean.getPageSize();
        String cachedData = redisService.get(cacheKey);
        // 构造查询条件
        Pagination pagination = new Pagination();
        pagination.setList(new ArrayList<>());
        int countNum = getSummaryDataList(bean, false).size();
        if (countNum > 0) {
            pagination.setPageNo(bean.getPageNo());
            pagination.setPageSize(bean.getPageSize());
            pagination.setTotalCount(getSummaryDataList(bean, false).size());
            List<ComDataStatisticsVO> comDataStatisticsVOS = getSummaryDataList(bean, true);
            if (ObjectUtil.isNotEmpty(comDataStatisticsVOS)) {
                pagination.setList(comDataStatisticsVOS);
            }
        }
        redisService.setex(cacheKey, 60 * 5, JSONUtil.toJsonStr(pagination));
        log.info("ComDataStatisticsService-pageForSummary-list-耗时: {}ms", System.currentTimeMillis() - startTime);
        return pagination;
    }

    /**
     * 导出每日数据
     *
     * @param bean
     * @return
     */
    public void exportForSummaryUser(SqfwCustomForAdminCommonVO bean, HttpServletResponse response) {
        SqfwCustomForAdminCommonVO sqfwCustomForAdminCommonVO = new SqfwCustomForAdminCommonVO();
        BeanUtil.copyProperties(bean, sqfwCustomForAdminCommonVO);
        if (null != bean && (ObjectUtil.isEmpty(sqfwCustomForAdminCommonVO.getQueryStartTime()) || ObjectUtil.isEmpty(sqfwCustomForAdminCommonVO.getQueryEndTime()))) {
            LocalDateTime nowDateTime = LocalDateTimeUtil.now();
            sqfwCustomForAdminCommonVO.setQueryEndTime(nowDateTime);
            sqfwCustomForAdminCommonVO.setQueryStartTime(nowDateTime.minusDays(30));
        }
        long startTime = System.currentTimeMillis();
        try {
            List<ComDataStatisticsVO> comDataStatisticsVOS = getSummaryDataList(sqfwCustomForAdminCommonVO, false);
            ExcelUtil<ComDataStatisticsVO> util = new ExcelUtil<>(ComDataStatisticsVO.class);
            util.exportExcel(response, comDataStatisticsVOS, "社区和用户数据");
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        log.info("ComDataStatisticsService-exportForSummaryUser-list-耗时: {}ms", System.currentTimeMillis() - startTime);
    }

    private List<ComDataStatisticsVO> getSummaryDataList(SqfwCustomForAdminCommonVO bean, Boolean countFlg) {
        SqfwCustomForAdminCommonVO finalResult = new SqfwCustomForAdminCommonVO();
        Sql sql = Sqls.create(buildQueryCondSummary(bean, countFlg));
        log.info("ComDataStatisticsService-list-SQL: {}", sql.toString());
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                List<ComDataStatisticsVO> comDataStatisticsVOS = new ArrayList<ComDataStatisticsVO>();
                while (rs.next()) {
                    ComDataStatisticsVO comDataStatisticsVO = new ComDataStatisticsVO();
                    for (int i = 0; i < columnCount; i++) {
                        String date = rs.getString("date");
                        int summaryDailyCount = rs.getInt("summary_daily_count");
                        int summaryCumulativeCount = rs.getInt("summary_cumulative_count");
                        int userDailyCount = rs.getInt("user_daily_count");
                        int userCumulativeCount = rs.getInt("user_cumulative_count");
                        comDataStatisticsVO.setDate(date == null ? "" : date.substring(0, 10));
                        comDataStatisticsVO.setSummaryDailyCount(summaryDailyCount);
                        comDataStatisticsVO.setSummaryCumulativeCount(summaryCumulativeCount);
                        comDataStatisticsVO.setUserDailyCount(userDailyCount);
                        comDataStatisticsVO.setUserCumulativeCount(userCumulativeCount == 0 ? countSysUserData(comDataStatisticsVO.getDate()) : userCumulativeCount);
                    }
                    comDataStatisticsVOS.add(comDataStatisticsVO);
                }
                return comDataStatisticsVOS;
            }
        });
        sqfwFormsummaryNewService.execute(sql);
        return sql.getList(ComDataStatisticsVO.class);
    }

    public String buildQueryCondSummary(SqfwCustomForAdminCommonVO bean, Boolean countFlg) {
        Criteria cri = Cnd.cri();
        SqlExpressionGroup criSql = cri.where();
        // 不传时间参数，显示最多统计一个月
        if (ObjectUtil.isEmpty(bean.getQueryStartTime())) {
            LocalDateTime nowDateTime = LocalDateTimeUtil.now();
            bean.setQueryEndTime(nowDateTime);
            bean.setQueryStartTime(nowDateTime.minusDays(15));
        }
        // 时间
        StringBuffer sbCond = new StringBuffer();
        if (ObjectUtil.isNotEmpty(bean.getQueryStartTime()) && ObjectUtil.isNotEmpty(bean.getQueryEndTime())) {
            sbCond.append(" AND t1.date >= '").append(formatStartQueryTime(bean.getQueryStartTime())).append("' AND t1.date <= '").append(formatStartQueryTime(bean.getQueryEndTime())).append("'");
        }
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t1.date AS date, t1.daily_count AS summary_daily_count, t1.cumulative_count AS summary_cumulative_count, t2.daily_count AS user_daily_count, t2.cumulative_count AS user_cumulative_count FROM (SELECT date_trunc('day', create_date) AS date, COUNT(*) AS daily_count, SUM(COUNT(*)) OVER (ORDER BY date_trunc('day', create_date)) AS cumulative_count FROM shequ.\"sqfw_form_summary_new\" GROUP BY date_trunc('day', create_date)) t1 ");
        sb.append(" LEFT JOIN (SELECT date_trunc('day', TO_TIMESTAMP(createdat::bigint / 1000)) AS date, COUNT(*) AS daily_count, SUM(COUNT(*)) OVER (ORDER BY date_trunc('day', TO_TIMESTAMP(createdat::bigint / 1000))) AS cumulative_count FROM shequ.\"sys_user\" GROUP BY date_trunc('day', TO_TIMESTAMP(createdat::bigint / 1000))) t2 ON t1.date = t2.date ");
        sb.append(" WHERE 1 = 1 ").append(sbCond.toString());
        sb.append(" ORDER BY t1.date DESC ");
        if (countFlg) {
            Integer pageNo = bean.getPageNo();
            Integer pageSize = bean.getPageSize();
            if (1 == pageNo) {
                pageNo = 0;
            }
            if (pageNo > 1) {
                pageNo = (pageNo - 1) * pageSize;
            }
            if (0 != pageSize) {
                sb.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(pageNo);
            }
        }
        return sb.toString();
    }


    public static String formatStartQueryTime(LocalDateTime startQueryTime) {
        if (startQueryTime != null) {
            return LocalDateTimeUtil.format(startQueryTime, DatePattern.NORM_DATE_PATTERN);
        }
        return DateUtil.formatDate(new Date("1970-01-01"));
    }

    // 结束时间处理
    public static String formatEndQueryTime(LocalDateTime endQueryTime) {
        if (endQueryTime != null) {
            return LocalDateTimeUtil.format(endQueryTime, DatePattern.NORM_DATE_PATTERN);
        }
        return DateUtil.formatDate(new Date());
    }

    /**
     * 查询数据总数
     *
     * @return
     */
    public static Integer countSysUserData(String dateTime) {
        if (StringUtils.isEmpty(dateTime)) {
            return 0;
        }
        Dao dao = (Dao) NutzIocUtils.getBeanByType(Dao.class);
        StringBuffer sb = new StringBuffer("SELECT COUNT(1) FROM shequ.\"sys_user\" WHERE substring(TO_TIMESTAMP(createdat::bigint / 1000)::text,0,11) <= @dateTime ");
        Sql sql = Sqls.create(sb.toString());
        sql.setParam("dateTime", dateTime);
        sql.setCallback(Sqls.callback.integer());
        dao.execute(sql);
        return sql.getInt();
    }
}
